import threading
from sqlalchemy import create_engine, Column, Integer, String, JSON  , text 
from sqlalchemy.orm import declarative_base  
from sqlalchemy.orm import scoped_session, sessionmaker
# 数据库操作类


Models = declarative_base()  

class STATUS:
    error = 0
    success = 1
    in_request = 1002
    failed_request = -1

    error_msg = '失败'
    success_msg = '成功'
    in_request_msg = '请求中'
    failed_request_msg = '请求失败'


# 创建一个基础模型类  
class UrlModel(Models):  
    __tablename__ = 'urls'  
      
    id = Column(Integer, primary_key=True)  
    url = Column(String, unique=True, nullable=False)  
    filepath = Column(String, nullable=True)  
    status = Column(Integer, nullable=False)  
    data = Column(JSON, nullable=True)  

# 模型
class Model:
    _session = threading.local()
    def __init__(self) -> None:
        # 创建数据库引擎（这里以SQLite的内存数据库为例，你也可以使用文件路径）  
        self.engine = create_engine('sqlite:///sql.db')  

        # 创建所有定义的表  
        Models.metadata.create_all(self.engine)  
        
        
        # 创建会话类  
        self.Session = sessionmaker(bind=self.engine)  
        self.session = self.Session()  
        self.session.execute(text("PRAGMA busy_timeout=100000000;"))

    def close(self):
        self.session.close()
        self.engine.dispose()

    def get_session(self):
        DbSession = sessionmaker(bind=self.engine)
        session = DbSession()
        return session

if __name__ == "__main__":
    a = Model()

    if not a.session.query(UrlModel).filter_by(url='http://baidu.com').first():
        new_data = UrlModel(url='http://baidu.com', filepath='page/index.html', status=STATUS.success, data=[['a.png','replace','http://aa...','img/aa.jpg']])  
        a.session.add(new_data)  
        a.session.commit()  

    if not a.session.query(UrlModel).filter_by(url='http://baidu.com2').first():
        new_data = UrlModel(url='http://baidu.com2', filepath='page/index.html', status=STATUS.in_request, data=[['a.png','replace','http://aa...','img/aa.jpg']])  
        a.session.add(new_data)  
        a.session.commit()  


    # 查询
    users = a.session.query(UrlModel).all()
    for user in users:  
        print(f"ID: {user.id}, url: {user.url}, filepath: {user.filepath}, status: {user.status}, data: {user.data}")  

    
    # 修改
    a.session.query(UrlModel).filter(UrlModel.url == "http://baidu.com2").update({"filepath": "page/demo.html"})
    a.session.commit()  

    # 删除
    a.session.query(UrlModel).filter(UrlModel.url == "http://baidu.com2").delete()
    a.session.commit()  

    # 关闭会话  
    a.session.close()
